* Cleaned data from the school questionaires

use "Data/NCDS/UKDA-5565-stata11/stata11/ncds0123.dta", clear
label variable n149  "1S Childs school attendance-% half-days"
qui ds, not(varl "ncdsid serial number"|varl "1S *"|varl "2S *"|varl "3S *")
drop `r(varlist)'

label define nym 0 "No" 1 "Yes" 99 "Missing", replace

* rename serial number
rename ncdsid  NCDSID  

********************************************************************************
* school and pupil char at 7 and 11
********************************************************************************

* State school (i.e., non-independent school)
ge StateSch_7=1 if n22==1|n22==3|n22==4
replace	StateSch_7=0 if n22>0 & StateSch_7==. & n22!=.
ge MissingStateSch_7=StateSch_7==. 
label variable StateSch_7 "School type is State" 
label variable MissingStateSch_7 "Missing state school "
su StateSch_7
replace StateSch_7=r(mean) if StateSch_7==.

ge StateSch_11=1 if (n824>=1 & n824<=8)|n825==3|n825==4|n825==5|n825==7
replace StateSch_11=0 if n825==1
ge MissingStateSch_11=StateSch_11==. 
label variable StateSch_11 "School type is State" 
label variable MissingStateSch_11 "Missing state school "
su StateSch_11
replace StateSch_11=r(mean) if StateSch_11==.

* school size
rename n24 iSchSize_7
replace iSchSize_7=. if iSchSize_7<0
winsor iSchSize_7, generate(SchSize_7) p($p_winsior)
ge MissingSchSize_7=SchSize_7==. 
label variable MissingSchSize_7 "Missing number of pupils in the school "
label variable SchSize_7 "Number of pupils in the school" 
su SchSize_7
replace SchSize_7=r(mean) if SchSize_7==.


rename n829 iSchSize_11
replace iSchSize_11=. if iSchSize_11<0
winsor iSchSize_11, generate(SchSize_11) p($p_winsior)
ge MissingSchSize_11=SchSize_11==. 
label variable MissingSchSize_11 "Missing number of pupils in the school "
label variable SchSize_11 "Number of pupils in the school" 
su SchSize_11
replace SchSize_11=r(mean) if SchSize_11==.

* Size of child's class
rename n46 iclasssize_7
recode iclasssize_7 (-1=.)
winsor iclasssize_7, generate(classsize_7) p($p_winsior)
ge Missingclasssize_7=classsize_7==. 
label variable Missingclasssize_7 "Missing number of pupils in the class "
label variable classsize_7 "Number of pupils in the class" 
su classsize_7
replace classsize_7=r(mean) if classsize_7==.

rename n865 iclasssize_11
recode iclasssize_11 (-1=.)
winsor iclasssize_11, generate(classsize_11) p($p_winsior)
drop iclasssize_11
ge Missingclasssize_11=classsize_11==. 
label variable Missingclasssize_11 "Missing number of pupils in the class "
label variable classsize_11 "Number of pupils in the class" 
su classsize_11
replace classsize_11=r(mean) if classsize_11==.

* Child's class grouping based on ability 
ge SchAbility_7=1 if n48==5|n48==6|n48==7
replace SchAbility_7=0 if n48>0 &  SchAbility_7!=1 & n48!=.
ge MissingSchAbility_7=SchAbility_7==. 
label variable MissingSchAbility_7 "Missing child's class grouping based on ability "
label variable SchAbility_7 "Class grouping based on ability " 
su  SchAbility_7
replace SchAbility_7=r(mean) if  SchAbility_7==.

ge SchAbility_11=1 if n862==1|n862==2|n862==3
replace SchAbility_11=0 if n862>0 &  SchAbility_11!=1 & n862!=.
ge MissingSchAbility_11=SchAbility_11==. 
label variable MissingSchAbility_11 "Missing child's class grouping based on ability "
label variable SchAbility_11 "Class grouping based on ability" 
su  SchAbility_11
replace SchAbility_11=r(mean) if  SchAbility_11==.

* Childs attendance rate at 7 and 11 
rename n149 iattendance_7
replace iattendance_7=. if iattendance_7<0
winsor iattendance_7, generate(Pupattendance_7) p($p_winsior)
drop iattendance_7
ge MissingPupattendance_7=Pupattendance_7==. 
label variable MissingPupattendance_7 "Missing child's attendance rate "
label variable Pupattendance_7 "Attendance rate"
su  Pupattendance_7
replace Pupattendance_7=r(mean) if Pupattendance_7==.

rename n870 possatt
replace possatt=. if possatt<=0
rename n874 absence
replace absence=. if absence<=0
ge iattendance_11=100*(possatt-absence)/possatt
drop absence possatt
replace iattendance_11=. if iattendance_11<0
winsor iattendance_11, generate(Pupattendance_11) p($p_winsior)
drop iattendance_11
ge MissingPupattendance_11=Pupattendance_11==. 
label variable MissingPupattendance_11 "Missing child's attendance rate "
label variable Pupattendance_11 "Attendance rate"
su  Pupattendance_11
replace Pupattendance_11=r(mean) if Pupattendance_11==.

* Received help for special educational needs 7 and 11
ge ESN_7=1 if n35==1
replace ESN_7=0 if n35==2|n35==3
ge MissingESN_7=ESN_7==. 
label variable MissingESN_7 "Missing child received help for special educational needs"
label variable ESN_7 "Help for special educational needs "
su  ESN_7
replace ESN_7=r(mean) if ESN_7==.

ge ESN_11=1 if n853==1
replace ESN_11=0 if n853==2|n853==3
ge MissingESN_11=ESN_11==. 
label variable MissingESN_11 "Missing child received help for special educational needs"
label variable ESN_11 "Help for special educational needs"
su  ESN_11
replace ESN_11=r(mean) if ESN_11==.

* Native English speaker
ge NativeEnglish_7=1 if n89==4
replace NativeEnglish_7=0 if n89==2|n89==3
ge MissingNativeEnglish_7 = NativeEnglish_7 ==. 
su NativeEnglish_7
replace NativeEnglish_7=r(mean) if NativeEnglish_7==.
label variable MissingNativeEnglish_7 "Missing native English speaker"
label variable NativeEnglish_7 "Native English speaker "

ge NativeEnglish_11=1 if n885 ==3
replace NativeEnglish_11=0 if n885==1|n885==2
ge MissingNativeEnglish_11 = NativeEnglish_11 ==. 
su NativeEnglish_11
replace NativeEnglish_11=r(mean) if NativeEnglish_11==.
label variable MissingNativeEnglish_11 "Missing native English "
label variable NativeEnglish_11 "Native English speaker "

* parent-teacher association in school (age 7 only)
rename n27 PTA
recode PTA (-1 = .) (2 = 0)
ge MissingPTA = PTA == .
su PTA
replace PTA = r(mean) if PTA == .
label variable PTA "Parent-teacher association in school "
label variable MissingPTA "Missing parent-teacher association in school"

* Social class of class dads (age 7 only)
ge NDadSC13N = n49
ge NDadSC3M4 = n51
ge NDadSC5 = n53
ge NDadUK = n55

local sc NDadSC13N NDadSC3M4 NDadSC5 NDadUK
foreach v of local sc{
replace `v' = . if `v'<0 | `v'==99
}

egen NDads = rowtotal(NDadSC13N NDadSC3M4 NDadSC5 NDadUK), missing
foreach v of local sc{
replace `v' = . if NDads<5
}
drop NDads
egen NDads = rowtotal(NDadSC13N NDadSC3M4 NDadSC5 NDadUK), missing
ge MissingDads = NDads==.
label variable MissingDads "Missing social class of class fathers"

foreach v of local sc{
ge p`v' = `v'/NDads
su p`v'
replace p`v' = r(mean) if p`v' ==.
}

label variable pNDadSC13N "Fraction of fathers of class pupils in \\ \ \ \ social class I, II or IIIN"
label variable pNDadSC3M4 "Fraction of fathers of class pupils in \\ \ \ \ social class IIIM or IV "
label variable pNDadSC5 "Fraction of fathers of class pupils in \\ \ \ \ social class V "
label variable pNDadUK "Fraction of fathers of class pupils with unknown social class"

* Parent-teacher meetings (age 7 only)
rename n57 NkidsParTea
recode NkidsParTea (-1 99= .)
ge pNkidsParTea = NkidsParTea/iclasssize_7
replace pNkidsParTea=. if pNkidsParTea>1 & pNkidsParTea!=.
ge MissingpNkidsParTea=pNkidsParTea==.
su pNkidsParTea
replace pNkidsParTea = r(mean) if pNkidsParTea==.
label variable MissingpNkidsParTea "Missing proportion of parents who met class teacher"
global ll 

label variable pNkidsParTea "Prop. of parents of class pupils\\ \ \ \ who met class or head teacher"

********************************************************************************
* Benefit from education beyong age 16?
********************************************************************************

local a S16
rename n2286 TBenPost16_`a'
recode TBenPost16_`a' (-1=.) (2=0) (3=1)
label define n2286 0 "No" 1 "Yes or Maybe", replace

********************************************************************************
* Sutiable for a univeristy course 
********************************************************************************

ge TBenDeg_`a'=1 if n2285==1
replace TBenDeg_`a'=0 if TBenDeg_`a'!=1 & n2285>0 & n2285!=.

********************************************************************************
* finish up
********************************************************************************

keep NCDSID Sch* *Pup* *ESN* *NativeEnglish* StateSch*  Missing* PTA ///
pNDadSC13N pNDadSC3M4 pNDadSC5 pNDadUK pNkidsParTea classsize_7 classsize_11 *S16

sort NCDSID 
save scratchdata/ncds0123_school711.dta, replace


/*
********************************************************************************
* Subjects studied at 16
********************************************************************************

rename n2209 Sub1
rename n2212 Sub2
rename n2215 Sub3
rename n2218 Sub4
rename n2221 Sub5
rename n2224 Sub6
rename n2227 Sub7
rename n2230 Sub8
rename n2233 Sub9
rename n2236 Sub10
rename n2239 Sub11
rename n2242 Sub12

egen IEnglish_`a'=anycount(Sub*), value(20 21 22)
egen IMath_`a'=anycount(Sub*), value(50 51 52 53 54 55 56 57 58 59)
egen IArt_`a'=anycount(Sub*), value(26 27 28 28)

label define sub16 1 "Languages" 2 "Arts/Hum." 3 "Social Sci" 4 "Practical" 5 "Math" 6 "Science", replace 
forvalues j=1/12{
recode  Sub`j' (-1=.) 
replace Sub`j'=. if Sub`j'>=80
replace Sub`j'=floor(Sub`j'/10)
recode Sub`j' (0=1) (7=4)
label values Sub`j' sub16

}

/*
********************************************************************************
* Teacher ability ratings by subject (age 16)
********************************************************************************


rename n2244 AbTeMath_`a'
rename n2245 AbTeEnglish_`a'
rename n2246 AbTeModLang_`a'
rename n2247 AbTeScience_`a'
rename n2248 AbTePractical_`a'
rename n2249 AbTeSocialSci_`a'

label define teacherabrating 1 "Below average" 2 "Average" 3 "Above average", replace 
local sub Math English ModLang Science Practical SocialSci
foreach s of local sub{
recode AbTe`s' (-1 6=.) (1=5) (2=4) (4=2) (5=1) 
label values AbTe`s' teacherabrating

ge IAbAvTe`s'_`a'=AbTe`s'==4|AbTe`s'==5 if AbTe`s'!=. 
ge IAlAvTe`s'_`a'=AbTe`s'==3|AbTe`s'==4|AbTe`s'==5 if AbTe`s'!=. 
}



********************************************************************************
* Teacher's assessment of expected first job
********************************************************************************

rename  n2291 Exp1stJob_`a'
recode Exp1stJob_`a' (-1 8 9 =.) (0 7=5)
replace Exp1stJob_`a'=6 if n2292==17 /* reallocate sports*/ 

ta Exp1stJob_`a', gen(IExp1stJob_`a')
rename IExp1stJob_`a'* IExp1stJob*_`a'
*/

egen ForLang_`a'= anycount(Sub*), value(1)
egen Arts_`a'= anycount(Sub*), value(2)
egen MathSc_`a'=anycount(Sub*), value(5 6)
egen SocSci_`a'=anycount(Sub*), value(3)
egen Pract_`a'=anycount(Sub*), value(4)

local vars IArt ForLang Arts MathSc SocSci Pract
foreach v of local vars{
replace `v'=. if Sub1==.
replace `v'=. if IEnglish==0
replace `v'=. if IMath==0
replace `v'=5 if `v'>5 & `v'!=.
}
drop IEnglish IMath 
drop Sub1-Sub12
*
